﻿CREATE PROCEDURE [dbo].[GetBrandSiteMapPath] 
	@brandId int = 0,
	@productId int = 0
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	declare @t table
	(
		CategoryId int,
		Title varchar(255), 
		Parent int,
		[Description] varchar(8000),
		UrlId varchar(50)
	)
	if (@productId <> 0)
	begin
		insert into @t (CategoryId, Title, Parent)
		select ProductId, Title, BrandId from Products
		where ProductId = @productId
		select @brandId = Parent from @t where CategoryId = @productId
	end;
	insert into @t (CategoryId, Title, Description) 
		select BrandId, Title, Description from Brands
		where BrandId = @brandId

	declare @parent int
	set @parent = 3
	while (select count(*) from Categories where CategoryId = @parent) > 0
	begin
		insert into @t (CategoryId, Title, Parent, UrlId) 
		select CategoryId, Title, Parent, UrlId from Categories
		where CategoryId = @parent
		select @parent = Parent from @t where CategoryId = @parent
	end;
	select * from @t
end


